MySQL Online DDL 工具之Pt-Online-Schema-Change

Contents

MySQL DDL:

DDL是一个令所有MySQL DBA诟病的一个功能，因为在MySQL中在对表进行DDL时，会锁表，当表比较小比如小于1W行时，对前端影响较小，当时遇到千万级别的表，就会影响前端应用对表的写操作！

InnoDB引擎是通过以下步骤来进行DDL的：
1、按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
2、在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3、执行insert into tmp_table select * from original_table
4、rename original_table和tmp_table，最后drop original_table
5、释放 write lock。
可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。为此 Percona 推出一个工具 pt-online-schema-change，其特点是修改过程中不会造成读写阻塞。

工作原理：

如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。
1 创建一个和你要执行 alter 操作的表一样的空表结构。
2 执行表结构修改，然后从原表中的数据到copy到表结构修改后的表，
3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作更新到新表.
注意:如果表中已经定义了触发器这个工具就不能工作了。
4 copy 完成以后，用rename table 新表代替原表，默认删除原表。

用法介绍：

pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
–dry-run
这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
–execute
这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

依赖条件:

操作的表必须有主键否则报如下错误:

[mysql@mysql ~]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vname varchar(20)' --execute D=test,t=michael
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`michael`...
Creating new table...
Created new table test._michael_new OK.
Altering new table...
Altered `test`.`_michael_new` OK.
Dropping new table...
Dropped new table OK.
`test`.`michael` was not altered.
The new table `test`.`_michael_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

字段vname没有添加成功！

mysql> show create table michael\G
*************************** 1. row ***************************
       Table: michael
Create Table: CREATE TABLE `michael` (
  `id` int,
  `region_id` bigint(20),
  `cityCode` varchar(6),
  `address` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

添加主键：

mysql> alter table michael modify id int unsigned primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


[mysql@mysql ~]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vname varchar(20)' --execute D=test,t=michael
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`michael`...
Creating new table...
Created new table test._michael_new OK.
Altering new table...
Altered `test`.`_michael_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 2993 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_michael_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`michael`.
[mysql@mysql ~]$ 


mysql> show create table michael\G
*************************** 1. row ***************************
       Table: michael
Create Table: CREATE TABLE `michael` (
  `id` int,
  `region_id` bigint(20),
  `cityCode` varchar(6),
  `address` varchar(255),
  `vname` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

添加多个字段：

[mysql@mysql ~]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add column vrname varchar(20)','add column aname varchar(20),add column bname varchar(30)' --execute D=test,t=guoqing
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`guoqing`...
Creating new table...
Created new table test._guoqing_new OK.
Altering new table...
Altered `test`.`_guoqing_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 2964 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_guoqing_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`guoqing`.
[mysql@mysql ~]$ 


mysql> show create table michael\G
*************************** 1. row ***************************
       Table: michael
Create Table: CREATE TABLE `michael` (
  `id` int,
  `region_id` bigint(20),
  `cityCode` varchar(6),
  `address` varchar(255),
  `vname` varchar(20),
  `vrname` varchar(20),
  `aname` varchar(20),
  `bname` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除字段：

[mysql@mysql ~]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='drop column vrname','drop column aname,drop column bname' --execute D=test,t=guoqing                                    
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`guoqing`...
Creating new table...
Created new table test._guoqing_new OK.
Altering new table...
Altered `test`.`_guoqing_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 3013 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_guoqing_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`guoqing`.
[mysql@mysql ~]$

添加索引：

[mysql@mysql ~]$ pt-online-schema-change -uroot -proot -h10.10.10.188 --alter='add key weibo_idx(weibo)' --execute D=test,t=guoqing                                            
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`guoqing`...
Creating new table...
Created new table test._guoqing_new OK.
Altering new table...
Altered `test`.`_guoqing_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 3013 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_guoqing_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`guoqing`.
[mysql@mysql ~]$